#!/root/.pyenv/versions/ansible-env/bin/python3.7
import  json
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import sessionmaker


engine = create_engine(
    'mysql+pymysql://root:123456@localhost/myansi',
    encoding='utf8',echo=False
)
Base = declarative_base()
Session = sessionmaker(bind=engine)


class HostGroup(Base):
    __tablename__ = 'webansi_hostgroup'
    id = Column(Integer,primary_key=True)
    group_name = Column(String(30),unique=True)
    def __str__(self):
        return self.group_name

class Host(Base):
    __tablename__ = 'webansi_host'
    id = Column(Integer,primary_key=True)
    hostname = Column(String(50),unique=True)
    ipaddr = Column(String(15))
    hostgroup_id = Column(Integer,ForeignKey('webansi_hostgroup.id'))
    def __str__(self):
        return "<%s: %s>" % (self.hostname,self.ipaddr)


if __name__ == '__main__':
    session = Session()
    groups = session.query(HostGroup.group_name,Host.ipaddr).join(Host,HostGroup.id==Host.hostgroup_id)
    # print(groups.all())

    result = {}
    for group, host in groups.all():
        if group not  in result:
            result[group] = {}
            result[group]['hosts'] = [host]
        else:
            result[group]['hosts'].append(host)
    print(json.dumps(result))

    #
    # for group in groups:
    #     result[group.group_name] = {}
    #     result[group.group_name]['hosts'] = []
    #     hosts = group.host_set.all()
    #     for host in hosts:
    #         result[group.group_name]['hosts'].append(host.ipaddr)
    #
    # print(json.dumps(result))
